package com.etc.dao.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.etc.dao.AnnouncementDao;
import com.etc.entity.Announcement;
import com.etc.entity.Moments;
import com.etc.entity.User;
import com.etc.util.DBUtil;
import com.etc.util.PageData;

public class AnnouncementDaoImpl implements AnnouncementDao{

	@Override
	public List<Announcement> queryAllAnnouncement() {
		String sql="SELECT * FROM announcement order by a_time desc";
		CachedRowSet set = DBUtil.execQuery(sql);
		Announcement announcement=null;
		List<Announcement> list =new ArrayList<Announcement>();
		try {
			while (set.next()) {
				int a_id = set.getInt(1);
				User a_user=null;
				try {
					a_user = (User) byte2obj(set.getBytes(2));
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				String a_title = set.getString(3);
                String a_content = set.getString(4);
                String a_time = set.getString(5);
                 announcement = new Announcement(a_id, a_user, a_title, a_content, a_time);
                 list.add(announcement);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public boolean addAnnouncement(Announcement announcement) {
		String sql="INSERT INTO announcement VALUES(null,?,?,?,NOW())";
		boolean b = false;
		try {
			b = DBUtil.executeUpdate(sql, obj2byte(announcement.getA_user()),announcement.getA_title(),announcement.getA_content());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return b;
	}

	@Override
	public boolean rmvAnnouncement(int id) {
		String sql="DELETE FROM announcement WHERE a_id=?";
		boolean b = DBUtil.executeUpdate(sql, id);
		return b;
	}

	@Override
	public Announcement queryAnnouncement(String name) {
		String sql="SELECT * FROM announcement where a_content=?";
		CachedRowSet set = DBUtil.execQuery(sql,name);
		Announcement announcement=null;
		try {
			while (set.next()) {
				int a_id = set.getInt(1);
				User a_user = set.getObject(2, User.class);
				String a_title = set.getString(3);
                String a_content = set.getString(4);
                String a_time = set.getString(5);
                announcement = new Announcement(a_id, a_user, a_title, a_content, a_time);
       
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return announcement;
	}
	@Override
	public List<Announcement> queryNewAnnouncement() {
		String sql="SELECT * FROM announcement order by a_time desc limit 0,100";
		CachedRowSet set = DBUtil.execQuery(sql);
		Announcement announcement=null;
		List<Announcement> list =new ArrayList<Announcement>();
		try {
			while (set.next()) {
				int a_id = set.getInt(1);
				User a_user=null;
				try {
					a_user = (User) byte2obj(set.getBytes(2));
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				String a_title = set.getString(3);
                String a_content = set.getString(4);
                String a_time = set.getString(5);
                 announcement = new Announcement(a_id, a_user, a_title, a_content, a_time);
                 list.add(announcement);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 将Java对象序列化为byte[]
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public static byte[] obj2byte(Object obj) throws Exception {
	    byte[] ret = null;
	    ByteArrayOutputStream baos = new ByteArrayOutputStream();
	    ObjectOutputStream out = new ObjectOutputStream(baos);
	    out.writeObject(obj);
	    out.close();
	    ret = baos.toByteArray();
	    baos.close();
	    return ret;
	}

	/**
	 * 将byte[]反序列化为Java对象
	 * @param bytes
	 * @return
	 * @throws Exception
	 */
	public static Object byte2obj(byte[] bytes) throws Exception {
	    Object ret = null;
	    ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
	    ObjectInputStream in = new ObjectInputStream(bais);
	    ret = in.readObject();
	    in.close();
	    return ret;
	}

	/**
	 * 分页
	 */
	@Override
	public PageData queryAnnByLikeAndLimit(int pageNo, int pageSize, String alike) {
		List<Announcement> list =new ArrayList<Announcement>();
		Announcement announcement=null;
		String sql = "select * from announcement where a_title like ? limit ?,?";
		CachedRowSet crs = DBUtil.execQuery(sql,"%"+alike+"%",(pageNo-1)*pageSize,pageSize);
		try {
			while(crs.next()) {
					int a_id = crs.getInt(1);
					User a_user=null;
					try {
						a_user = (User) byte2obj(crs.getBytes(2));
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					String a_title = crs.getString(3);
	                String a_content = crs.getString(4);
	                String a_time = crs.getString(5);
	                 announcement = new Announcement(a_id, a_user, a_title, a_content, a_time);
	                 list.add(announcement);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int totalCount=0;
		String sql2="select count(*) from announcement where a_title like ?";
		CachedRowSet crs2 = DBUtil.execQuery(sql2,"%"+alike+"%");
		try {
			while(crs2.next()) {
				totalCount=crs2.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData pageData = new PageData(list, pageNo, pageSize, totalCount);
		return pageData;
	}

	
	/**
	 * 根据留言编号查询留言信息
	 */
	@Override
	public Announcement queryAnnById(int id) {
			String sql="select * from announcement where a_id=?";
			CachedRowSet crs = DBUtil.execQuery(sql, id);
			Announcement announcement=null;
			try {
				while(crs.next()) {
					int a_id = crs.getInt(1);
					User a_user=null;
					try {
						a_user = (User) byte2obj(crs.getBytes(2));
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					String a_title = crs.getString(3);
	                String a_content = crs.getString(4);
	                String a_time = crs.getString(5);
	                announcement = new Announcement(a_id, a_user, a_title, a_content, a_time);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return announcement;
	}

	/**
	 * 根据关键字查询
	 */
	@Override
	public List<Announcement> queryByTitleLike(String title) {
		String sql="select * from announcement where a_title like ?";
		CachedRowSet crs = DBUtil.execQuery(sql, "%"+title+"%");
		List<Announcement> list =new ArrayList<Announcement>();
		Announcement announcement=null;
		try {
			while(crs.next()) {
				int a_id = crs.getInt(1);
				User a_user=null;
				try {
					a_user = (User) byte2obj(crs.getBytes(2));
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				String a_title = crs.getString(3);
                String a_content = crs.getString(4);
                String a_time = crs.getString(5);
                announcement = new Announcement(a_id, a_user, a_title, a_content, a_time);
                list.add(announcement);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	
}
